﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SgAutoArchiveBrrDal.Linq
{
    /// <summary>
    /// 统计工作量
    /// </summary>
    public class Tongji_Dal
    {
        /// <summary>
        /// 统计工作量
        /// </summary>
        public static List<TongjiEntity> Tongji_WorkCount(string sd, string ed)
        {
            /// <summary>
            /// 查询语句
            /// </summary>
            string sql = "SELECT COUNT(*) as count, B.U_RNAME as name FROM T_CARDINFO A LEFT JOIN T_User B ON A.C_UNAME = B.U_ID  where";
            if(sd != "")
                sql = sql + " C_DJDATE>='" + sd + " 00:00:01' and";
            if (ed != "")
                sql = sql + " C_DJDATE<='" + ed + " 23:59:59'";
            if (sql.EndsWith("where"))
                sql = sql.Substring(0, sql.LastIndexOf("w"));
            else if (sql.EndsWith("and"))
                sql = sql.Substring(0, sql.LastIndexOf("a"));
            sql = sql + " GROUP BY B.U_RNAME";

            SgAutoArchiveBrrEntities conn = new SgAutoArchiveBrrEntities();
            string connstring = conn.Database.Connection.ConnectionString;
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            try
            {
                con.ConnectionString = connstring;
                con.Open();

                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, con);
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
                System.Data.DataSet ds = new System.Data.DataSet();
                da.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                {
                    List<TongjiEntity> list = new List<TongjiEntity>();
                    foreach(System.Data.DataRow dr in ds.Tables[0].Rows)
                    {
                        TongjiEntity en = new TongjiEntity();
                        en.Name = (dr["name"]==null ? "" : dr["name"].ToString());
                        string c = (dr["count"] == null ? "0" : dr["count"].ToString());
                        if (c == "")
                            c = "0";
                        try
                        {
                            en.Count = int.Parse(c);
                        }
                        catch { en.Count = 0; }
                        list.Add(en);
                    }
                    return list;
                }
                else
                    return null;
            }
            catch { return null; }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 统计查档类型
        /// </summary>
        public static List<TongjiEntity> Tongji_WorkArchiveType(string sd, string ed)
        {
            /// <summary>
            /// 查询语句
            /// </summary>
            string sql = "SELECT COUNT(*) as count, C_TYPENAME as name FROM T_CARDINFO  where";
            if (sd != "")
                sql = sql + " C_DJDATE>='" + sd + " 00:00:01' and";
            if (ed != "")
                sql = sql + " C_DJDATE<='" + ed + " 23:59:59'";
            if (sql.EndsWith("where"))
                sql = sql.Substring(0, sql.LastIndexOf("w"));
            else if (sql.EndsWith("and"))
                sql = sql.Substring(0, sql.LastIndexOf("a"));
            sql = sql + " GROUP BY C_TYPENAME";

            SgAutoArchiveBrrEntities conn = new SgAutoArchiveBrrEntities();
            string connstring = conn.Database.Connection.ConnectionString;
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            try
            {
                con.ConnectionString = connstring;
                con.Open();

                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, con);
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
                System.Data.DataSet ds = new System.Data.DataSet();
                da.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                {
                    List<TongjiEntity> list = new List<TongjiEntity>();
                    foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
                    {
                        TongjiEntity en = new TongjiEntity();
                        en.Name = (dr["name"] == null ? "" : dr["name"].ToString());
                        string c = (dr["count"] == null ? "0" : dr["count"].ToString());
                        if (c == "")
                            c = "0";
                        try
                        {
                            en.Count = int.Parse(c);
                        }
                        catch { en.Count = 0; }
                        list.Add(en);
                    }
                    return list;
                }
                else
                    return null;
            }
            catch { return null; }
            finally
            {
                con.Close();
            }
        }
    }

    public class TongjiEntity
    {
        public string Name { get; set; }
        public int Count { get; set; }
    }
}
